Technology for generating a model in response to user selection of data

ABSTRACT

Dynamically generating a model for a report includes presenting a user with source data in a unitary structure including rows and columns of data. Selections received from the user include first, second and third ones of the columns. A nesting structure of the second column data relative to the first column data indicates the second column data is of a dimension that is a level of the first column data. A lack of nesting structure of the third column data relative to the first column data indicates the third column data is of a dimension that is separate from a dimension of the first column data. In response to receiving the user selections, a computer system builder module generates a snowflake-schema-based, analytic processing model, wherein for the selected first, second and third columns the builder module automatically creates respective first, second and third tables and table mapping structures.

BACKGROUND

The field of the present invention concerns dynamically generating adata model for a report, and, more particularly, generating the modelresponsive to user selected portions of source data.

In a database context, dimensions are defined that categorize and labeldata elements. The dimensions also provide a structure that enablesfiltering (also known as “slicing”) and grouping (also known as“dicing”) of the data elements. Commonly used dimensions categorize eachdata element according to its relation to people, products, place andtime, for example. A numerical data element, which may be referred to asa “measure” or a “metric,” is related to other data elements in thedatabase by the defined dimensions. One measure may be a sales figure,for example, such as number of units or dollar amount of products sold.It is common that a sales measure is associated with customer andproduct dimensions, for example, wherein for each sale a customer buys aproduct. Organization of sales measures by these dimensions allowsfiltering the sales to in order to present sales for selected customersand then allows grouping those selected sales by product, for example.While the above example describes a framework wherein sales measure datahas customer data and product data dimensions, such a selected set ofdata may be referred to herein, according to another framework, as datain a sales dimension with customer and product “dimension hierarchies,”or simply “hierarchies” (also referred to as “levels”).

SUMMARY

Dynamically generating a model for a report includes presenting a userwith source data by a source application module, wherein the source datais available for producing a report and is presented to the user by thesource application module in a unitary structure including rows andcolumns of data. Selections are received from the user, including first,second and third ones of the columns. A nesting structure of the secondcolumn data relative to the first column data indicates the secondcolumn data is of a dimension that is a level of the first column data.A lack of nesting structure of the third column data relative to thefirst column data indicates the third column data is of a dimension thatis separate from a dimension of the first column data. In response toreceiving the user selections, a computer system builder modulegenerates a snowflake-schema-based, analytic processing model, whereinfor the selected first, second and third columns the builder moduleautomatically creates respective first, second and third tables andtable mapping structures. The tables are created regardless of anynesting structure of the first, second and third column data. Inresponse to nesting structure of the second column data relative to thefirst column data, the mapping structure for the second table designatesthat the second table is for a dimension that is a level of a dimensionof the first table. In response to the lack of nesting structure of thethird column data relative to the first column data, the mappingstructure for the third table designates that the third table is for adimension that is separate from the first table's dimension.

System and computer program products relating to the above-summarizedmethods are also described and claimed herein.

BRIEF DESCRIPTION OF THE DRAWINGS

The above described aspects of the present invention and others,including objects, forms, features and advantages, will become moreapparent from the following detailed description of illustrativeembodiments thereof, which is to be read in connection with theaccompanying drawings. The drawings are not to scale and are, togetherwith the detailed description, for clarity in facilitating theunderstanding of persons skilled in the related art. In the drawings:

FIG. 1 illustrates a networked computer environment, according toembodiments of the present invention;

FIG. 2 is a block diagram of devices shown in FIG. 1, according toembodiments of the present invention;

FIGS. 3A, 3B, 3C and 3D illustrate first second and third hierarchies,according to embodiments of the present invention;

FIGS. 4A, 4B, 4C and 4D illustrate various configurations of dataaccessibly by a source application module, which may be user selectedfor including in a report, according to embodiments of the presentinvention;

FIGS. 5A, 5B and 5C illustrate a model generated by a model buildermodule responsive to successive user selections of portions of datashown in FIG. 4A according to embodiments of the present invention;

FIGS. 6A, 6B and 6C illustrate a model generated by a model buildermodule responsive to successive user selections of portions of datashown in FIG. 4D, according to embodiments of the present invention;

FIGS. 7A, 7B and 7C illustrate a model generated by a model buildermodule responsive to successive user selections of portions of datashown in FIG. 4B, according to embodiments of the present invention; and

FIGS. 8A, 8B, 8C, and 8D illustrate a model generated by a model buildermodule responsive to successive user selections of portions of datashown in FIG. 4C, according to embodiments of the present invention.

FIG. 9 provides a flow chart illustrating actions by a computer system,according to embodiments of the present invention.

DETAILED DESCRIPTION

Detailed embodiments of the present invention are disclosed herein toillustrate claimed structures and methods. This invention may, however,be embodied in many different forms and should not be construed aslimited to the exemplary embodiments disclosed herein. Rather, theseexemplary embodiments are provided so that this disclosure will bethorough and complete and will fully convey the scope of this inventionto those skilled in the art. In the description, details of well-knownfeatures and techniques may be omitted to avoid unnecessarily obscuringthe presented embodiments.

An online analytical processing (“OLAP”) model is a powerful dataanalysis construct that fits with the above described database aspects.The model's data structure allows an OLAP application module to quicklyaggregate the data according to its pre-defined dimension andmeasurement attributes. This enables quick navigation through data ofthe model and enables quick, efficient execution of data analysis tasks.

Regarding structure of OLAP model data, one such model includes the dataitself in a multidimensional “hypercube.” The data in this model isreferred to as simply a “cube” and this type of model is referred to asan “OLAP cube model.” Another type of OLAP model, a dimensionallymodeled relational (“DMR”) model, does not contain the data itself. Itprovides OLAP functionality via metadata for data that is in arelational model. Regardless of whether an OLAP model contains theunderlying data itself or merely metadata, it indicates combinations ofproperties of the data and may be based upon a relational databasehaving a star schema, for example. A star schema has a central facttable and one or more dimension tables joined to the fact table.Accordingly, an OLAP model for a star schema relational model is builtaround a central fact table, which contains a set of measures anddescribes how to aggregate data from the fact table across the OLAPmodel's dimensions. OLAP models may also be based on a relationalsnowflake schema, for example. A snowflake schema is like a star schema,but with one or more dimensions defined by multiple tables. OLAP modelsmay also model and optimize other relational model schemas and otherdata sets for which a relational model does not exist.

Herein disclosed, according to embodiments of the present invention, isa builder module for performing a process that automates computer systemcreation of an OLAP model data set from less structured data, so thatcertain aspects of manual setup are not required by a human modelingexpert. As previously stated, the model's data structure allows an OLAPapplication module to quickly aggregate the data according to itspre-defined dimension and measurement attributes. In some embodiments ofthe present invention, the model's data structure that is built by themodule includes both a logical structure for a database and theunderlying data of the database, such as the database structures anddata as described in examples herein below. In other embodiments of thepresent invention, the model's data structure that is built by themodule includes the logical structure for the database but merely refersto the underlying data thereof, rather than including the underlyingdata in the model's data structure.

Further, the builder module, according to embodiments of the presentinvention, not only automatically creates the OLAP model organization ofthe data set, but it does so based on more than state of input datamerely at a time when the data is imported. Specifically, the buildermodule receives source data, also known as input data, which may includethe builder module importing source data and may include the buildermodule merely receiving user input that identifies source data asparticular data in an executing application module, such as particulardata in a spreadsheet or set of spreadsheets (i.e., rows, columns,sheets, or any combination thereof) or all data in a particularspreadsheet or set of spreadsheets, for example.

Then, after the source data has been thus received or identified, thebuilder module then collects information for a time about usage of thedata, which identifies which part of the source data the user intends touse or actually does use and how it is or will be used. In this context,data “usage” refers to the user performing operations on at least someof the source data in an executing application module, including any oneor more of operations such as data selecting, manipulating, querying,navigating, changing, displaying, etc. The builder module then uses thiscollected data usage information as OLAP model generation input foridentifying what part of the source data to include in the model and howto organize the data that is included, e.g., what dimension tables togenerate and how to define relationships among portions of the includeddata, so that when the user signals the builder module to generate themodel, the builder module is not limited to gross assumptions about usecases based merely on the source data by itself.

In this way, the OLAP model is not created based merely on static data,i.e., merely the state of the source data itself when imported for modelgeneration. This is in contrast to an arrangement in which organizationof the OLAP model data is defined by source data preprocessing at thetime when source data is imported, i.e., processing before the abovedescribed imported data usage, wherein the preprocessed data remainsorganized that way unless and until work is done to change the model,regardless of how the data is used after it is imported. While humaneffort for creating an OLAP model has been reduced somewhat by recenttools that assist in model definition when importing data, such as dataof a relational model or a spreadsheet, for example, these tools stillcreate a fixed OLAP model when the source data is imported withoutcollecting information about data usage subsequent to the importing. Insuch an arrangement in which the OLAP model data is defined at the timewhen source data is imported, if the model contains a time dimensionwith hierarchical year and month attributes and measures, for example,additional years of data may be added without changing the model and maythereby become available to users of the model. However, until the modelis changed, the OLAP application module's query and display of data islimited to the same year and month structure, and if the model has twodata items in different dimensions, it may not allow drilling down inthe data to navigate from one item to the other.

According to embodiments of the present invention, the model builderprocess delays creation of an OLAP model, wherein after importing orcreating data the model builder process waits to create the model untilthe process detects user interaction with source data that indicates howthe data will be used, which may include building the model in stages,such as at particular events during the course of a user building (i.e.,authoring) a report (i.e., defining which source data to include in areport), for example, which is before executing a query or command thatactually produces the report. (Herein building a report includesbuilding a view. That is, the term “report” encompasses a view. The term“report” also encompasses a query result presentation.) This differsfrom creating an OLAP model based on source data without the benefit ofinformation about usage of the source data gained from the process ofbuilding reports. According to embodiments of the present invention,stages of building a report are intertwined with stages of creating themodel and importing data identified for the report into the model. Onesuch report building stage may include the user selecting parts of thesource data for the report. For example, in a running spreadsheetapplication module, a user may select data for a report, such as data inselected columns and rows of the spreadsheet. Thus, the model buildermodule responsively creates an OLAP model stage-by-stage responsive touser selection of portions of source data for the report,piece-by-piece. That is, a first report generation stage occurs, whereinthe user selects first data. The first report generation stage isfollowed by a first model creation stage, wherein the first selecteddata is added to the model. Then a second report generation stageoccurs, in which second data is selected, which is followed by a secondmodel creation stage, etc.

In the manner described above, an expert modeler or automated systemdoes not create the model before the data is actually selected for usein a report, according to embodiments of the present invention. Instead,the builder module creates an OLAP model at report authoring time. Inthis way, the OLAP model tends to be minimal, since it contains onlywhat the user has selected for the report, plus any extra data that thebuilder module determines is likely in the same hierarchical structureas the data in current report, as described herein below. Also, themodel created by the builder module is persisted with the report theuser is creating.

Benefits arise from basing an OLAP model on nesting of data created by auser when the user generates report, such as in the following star andsnowflake schema model examples. The examples show how the user definesrelations among data items in the report and how the OLAP builder moduleuses this to define relations among data items for the OLAP model.

FIG. 1 illustrates an example computing environment 100, according toembodiments of the present invention. As shown, computing environment100 includes computer systems 110.1, 110.2 through 110.N and connectsvia network 120, which may be public or private. Systems 110.1, 110.2,etc. include data and application modules, such as model builderapplication module 103, model data 107, analytic application module 109source data 132 and source data application module 136, wherein theapplication modules are configured to perform tasks. (According toembodiments of the present invention modules 109 and 136 may be part ofthe same application.) Model builder 103 is configured to build modeldata 107 as described herein above, where the model building by module103 is responsive to user selection of portions of source data 132 insource data application module 136 and where model data 107 enablesanalytic application 109 to more efficiently query, analyze, manipulateand present the user selected portions of source data 132 in a report,as described herein below.

FIG. 2 illustrates details of a computer system 200 suitable as computersystems 110.1, 110.2, etc. according to embodiments of the presentinvention, wherein system 200 includes at least one central processingunit (CPU) 205, network interface 215, interconnect (i.e., bus) 217,memory 220 and storage device 230. CPU 205 may retrieve and executeprogramming instructions stored in memory 220. Similarly, CPU 205 mayretrieve and store application data residing in memory 220. Interconnect217 may facilitate transmission, such as of programming instructions andapplication data, among CPU 205, storage 230, network interface 215, andmemory 220. CPU 205 is representative of a single CPU, multiple CPUs, asingle CPU having multiple processing cores, and the like. Additionally,memory 220 is representative of a random access memory, which includesdata and program modules for run-time execution, such as model builder103, according to embodiments of the present invention. It should beunderstood that system 200 may be implemented by other hardware and thatone or more modules thereof may be firmware.

Model builder module 103, data model 107, analytic application 109 andsource data 132 shown in FIG. 1 may be run-time instances in memory 220of data and program modules from storage 230. Storage 230 may be a diskdrive storage device, for example. Although shown as a single unit,storage 230 may be a combination of fixed and/or removable storagedevices, such as fixed disc drives, floppy disc drives, tape drives,removable memory cards or optical storage, network attached storage(NAS), or a storage area-network (SAN).

The above-described examples and depictions in the Figures are not meantto imply architectural limitations. Further, embodiments of the presentinvention may reside on any data storage medium (i.e., floppy disk,compact disk, hard disk, tape, ROM, RAM, etc.) used by a computersystem. (The terms “computer,” “system,” “computer system,” and “dataprocessing system” are used interchangeably herein.)

Referring now to FIG. 4A, source data 401 imported into or created inthe spreadsheet application module, for example, includes data incolumns having headers with labels that indicate attributes includingcountries 402, Cities 406, regional Sales Representatives 404, Customers408 and product Sales 410 quantities. The values in the columns belowthe headers indicate a 1 to N mapping between Sales Representative data404 and Customer data 408 attributes, i.e., each Sales Representative404 has a number of Customers 408 and each Customer 408 has only oneSales Representative 404, as may be seen by the Customer/SalesRepresentative associations indicated by rows. For example, rows 2-6indicate that Sales Representative Jones has Customers Saetek,Felectrics, Treslink, Plusdexol and Haytrax, since Sales RepresentativeJones is in the same rows with those Customers. The source data valuesalso indicate by row associations that Sales Representatives Jones andSmith are each associated with only a single Country, i.e., USA. (In theillustrated instance, both are associated with the same Country.) Also,the single Country, USA has more than one Sales Representative, Jonesand Smith. So this is another 1 to N mapping. Further, the SalesRepresentatives 404 are associated with Cities 406. In the illustratedexample, each of the two Sales Representatives 404 are associated withmore than one City 406 and each City 406 has only one regional SalesRepresentative 404, which indicates another 1 to N mapping. Also, thesource data 401 indicates that Cities 406 and Customers 408 areassociated in a 1 to N mapping. Likewise, Country 401 and the particularCities 406 of this source data have a 1 to N mapping.

In alternative terminology, “1 to N” and “N to 1” are commonly describedas “one to many” and “many to one.” Likewise, “N to N” is commonlydescribed as “many to many.” However, it should be understood that inthis context “N” and “many” do not necessarily imply more than two,although it may happen in some cases that “N” entails many instances.

Given this underlying organization of the source data, there are severalpossible hierarchies that the user may create in a report, some of whichare illustrated in FIGS. 3A, 3B, 3C and 3D. In FIG. 3A, Cities 406 aresubordinate elements of Country 402, each City 406 belonging to oneCountry 402; and Customers 408 are subordinate elements of Cities 406,each Customer 408 belonging to one City 406. In FIG. 3B, SalesRepresentatives 404 are added to the hierarchy of FIG. 3A, wherein SalesRepresentatives 404 are subordinate elements of county 402, each SalesRepresentative 404 belonging to one Country 402; Cities 406 aresubordinate elements of Sales Representatives 404, each City 406belonging to one Sales Representative 404; and Customers 408 aresubordinate elements of Cities 406, each Customer 408 belonging to oneCity 406. In FIG. 3C, Cities 406 are omitted from the hierarchy of FIG.3B, so that Sales Representatives 404 are subordinate elements of theCountry 402, each Sales Representative 404 belonging to the one Country402; and Customers 408 are subordinate elements of Sales Representatives404, each Customer 408 belonging to one Sales Representative 404.

The model builder module creates a model based on nesting indicated bythe source data 401 portions that the user identifies for a report. In areport in which the user includes Sales Representatives 404, Cities 406and Customers 408, but not countries 402, for example, the buildermodule generates an OLAP model dimension that has a hierarchy like thelower portion of FIG. 3B, i.e., without countries.

More specifically, the first time the user selects a portion of sourcedata 401, the model builder module generates a first dimension for themodel. Responsive to the user selecting the Sales Representative 404column as initial data for the report, for example, the model buildermodule generates a Sales Representative dimension in the model. As thefirst data in the model, the Sales Representative data represents a newdimension. The model builder module is able to identify the selecteddata as Sales Representative data due to the column heading for the data404, which is in row 1.

Referring to FIGS. 4A and 5A, a model 500 is shown that is generated bya model builder module responsive to user selection of portions of data401 shown in FIG. 4A in a spreadsheet application module, according toembodiments of the present invention. Adding the Sales Representativedata 404 as first data for model 500 for a new report includes the modelbuilder generating a Sales Representative dimension table 502 for theselected data, as shown, which is thereby included in data structure ofthe model 500 as a first dimension. Since this is the first dataselected, the builder module creates a name 509 for a new schema 501 andadds the name 509 to a catalog 503 of the database, according toembodiments of the present invention. For tables that it createsresponsive to user selection of data for the new report, model builder103 associates the tables with the newly named schema 501. That is, forexample, when the user selects Sales Representative data 404, the modelbuilder executes a statement to create table 502 therefor, which adds alogical description 505 of table 502 to catalog 503 for the namedschema. Description 505 may also include, for example, a name for thetable that is unique among any tables and views that already exist inthe schema, an ordered list of declarations (name, data type) forcolumns of the table, and a list of table content constraints.

In the herein described embodiments of FIGS. 5A through 5C, 6A through6C and 7A through 7C, the model builder generates a snowflake schema,wherein for newly selected data the model builder generates a new table,regardless of whether the new data is for a new level of an existingdimension, and wherein model builder 103 generates, for the namedschema, a mapping data structure and a description thereof in catalog503, where the mapping data structure maps together any levels of acommon dimension. In another embodiment, illustrated in FIGS. 8A through8C herein, for example, the model builder generates a star schema, inwhich one dimension table may include more than one level of adimension, so that for newly selected data the model builder adds thedata to an existing dimension table if the model builder determines thenew data is for a new level of the existing dimension.

Next, responsive to the user selecting another set of data to thepresent report that is not a metric, e.g., Cities 406 column in thisexample, the model builder module generates a Cities level in model 500.Referring to FIG. 5B, this includes generating a Cities table 504 anddescription thereof in catalog 503 for the named schema 501, where table504 contains the selected Cities data 406, according to embodiments ofthe present invention. The model builder module is, once again, able toidentify the attribute to which the selected data is related, i.e.,Cities, due to the data's column heading. The model builder creates theCities level responsive to detecting that the current model does not yethave data for this attribute and also that the selected Cities data mapsin 1 to N fashion to the Sales Representative data already in the model.The 1 to N mapping indicates the Cities data may be added as a level tothe existing dimension created for Sales Representative data 404. Sincethe Sales Representative dimension is already in the model as dimensiontable 502, the model builder adds the Cities table 504 to the model as alevel associated with the previously added dimension table 502 for SalesRepresentatives 404 by adding a data structure 507 that indicates therelation, where data structure 507 may be a mapping table, and also bylinking keys. In the illustrated embodiment of FIGS. 5A through 5C, keysin the respective tables 502 and 504 for the Sales Representative data404 and Cities data 406 provide the linkages. (In different embodiments,the linking by keys may be done in different ways and may include keysin data structure 507.) The keys link respective Sales Representativesto respective Cities, according to the associations indicated by sourcedata 401 rows, wherein respective Sales Representatives 404 are insource data 401 rows with respectively associated Cities 406. In theillustrated embodiment of FIGS. 5A through 5C, the key that modelbuilder generates for Sales Representative Jones, i.e., Key1, matchesthe key generated for Cities La Follette, Whitney Point and Lino Lakes,which is also “Key1,” since Jones is on the same lines with those Citiesin source data 401, while the key that model builder generates for SalesRepresentative Smith, i.e., “Key2,” matches the key generated for CitiesBrookdale and Middletown, also “Key2,” since Smith is on the same lineswith those Cities in source data 401.

Next, responsive to the user selecting the Customer 408 column for thereport, the model builder module generates a Customer level in themodel. Referring to FIG. 5C, this includes generating a Customer table506 and description thereof in catalog 503 for the named schema andincludes adding the Customer level indication to data structure 507 forthe user selected Customer data 408, according to embodiments of thepresent invention, where the model builder is able to identify theattribute to which the selected data is related, i.e., Customers, due tothe data's column heading. Once again, the model builder creates thelevel responsive to detecting that the current model does not yet havedata for this attribute and also that the selected data maps in 1 to Nfashion to data that is already included in the model. In this case, themodel detects that the selected Customer data 408 maps in 1 to N fashionto the Sales Representative data 404 and also in 1 to N fashion to theCities data 406, so the model builder generates the Customer level insuch a way as to map the Customer data 408 to both Cities 406 and SalesRepresentatives 404. To do this, according to embodiments of the presentinvention, the model builder adds data to data structure 507 thatindicates the relation and provides linking keys. (As previously stated,in different embodiments the linking by keys may be done in differentways and may include keys in data structure 507.) In the presentlyillustrated embodiment, the linking adds portions to the keys in theCities table 504, so that the initially created portion of each key, asdescribed and illustrated for FIG. 5B, remains the same and stillmatches its respective key in the Sales Representative data 404 in table502, but the added portion of each City 406 key corresponds to acorresponding portion of the respective key that the model buildergenerates for the Customer table 506 to link each respective Customer408 to its City 406, as indicated by the source data 401 rows. Forexample, the key initially generated (FIG. 5B) for the City 406 of LaFollette was initially “Key1,” to match the key initially generated forSales Representative 404 Jones. When the Customer data 408 is added(FIG. 5C), a portion “A” is added to the key “Key1” that was initiallygenerated (FIG. 5B) for the City 406 of La Follette, yielding a revisedkey “Key1A.” Likewise, this same key “Key1A” is assigned to Customers406 Saetek and Felectrics in Customer table 506, since they areCustomers 408 in the City 406 of La Follette.

With this structure for the OLAP model data, a database applicationmodule is enabled to drill up and down for Sales Representatives 404along either or both of the City 406 and Customer 408 levels, andlikewise for Cities 406 along the Customer 408 level, as well as viceversa. The model correspondingly allows calculations that countCustomers 408 per Sales Representative 404, for example, where each suchcalculation is performed along a single path between two levels viacorresponding keys of respective Customers and Sales Representatives inthe Customer table 506 and Sales Representative table 502. Likewise, themodel also allows calculations that count Cities 406 per SalesRepresentative 404, for example, where each such calculation isperformed along a single path between two levels, since directCity-Sales Representative links are included in the model via the keysin the City table 504 that correspond to the keys in the SalesRepresentative table 502. Likewise, the model also allows calculationsthat count Cities 406 per Customer 408, for example, where each suchcalculation is performed along a single path between two levels, sincedirect City-Customer links are included in the model via the keys in theCustomer table 506 that correspond to the keys in the City table 504.

In the above examples, each Sales Representative has more than one City,but each City has only one Sales Representative, i.e., a 1 to N relationof Sales Representatives to Cities. In a different situation, where aCity has more than one Sales Representative, but each sales rep has asingle City, for example, i.e., a 1 to N relation of Cities to SalesRepresentative, then the model builder would switch these two levels.This is illustrated in FIGS. 4D and 6A through 6C, which also illustratethe user selecting data in a sequence other than top down in thehierarchy. (Certain actions performed and data structures created by themodel builder as described above are omitted in the followingdescriptions of model building illustrated in FIGS. 6A through 6C, FIGS.7A through 7C and FIGS. 8A through 8D. This is in order to focus onparticular issues, not to suggest that these actions and data structuresare omitted.)

Referring now to FIG. 6A together with FIGS. 3D and 4D, based on 1 to Nrelationships City data 406 is highest in a hierarchy of the illustratedCity 406, Customer 408, and Sales Representative 404 data. Next in thehierarchy is Sales Representatives 404, then Customers 408. The user inthe illustrated instance, however, first selects Customers 408 toinclude in a report, then Sales Representatives 404, then Cities 406.This illustrates that the model builder will, in this circumstance,still generate a dimension table 602 having Customer data 408 for model600 responsive to selection of the first data and will add to model 600(FIG. 6B) responsive to selection of the second data portion, for SalesRepresentatives 404, such that Sales Representatives 404 are a levelabove the Customers 408, as may be seen by comparison of the keys inCustomer table 602 and Sales Representative table 604 of FIG. 6B. Thatis, the model builder generates respectively different keys for eachrespective attribute at the higher level in the hierarchy, SalesRepresentatives, so that all the keys are unique in table 604. Regardingthe lower level in this example, i.e., Customers, the model buildergenerates the same key for each Customer as the Customer's respectiveSales Representative, so at least some keys are repeated in Customertable 602. In general, any object at a level that has more than oneobject below it in a next level down of a hierarchy must have a uniquekey.

Since the user selects the highest level last in this example, the modelbuilder revises the keys of the earlier generated tables 602 and 604differently than in the preceding example of FIGS. 5A through 5C, inwhich the user selected data in a top down fashion. In the presentexample, the model builder generates keys unique for each City in Citytable 606, where the beginning portion of each key is unique to eachCity, and the model builder modifies the keys of Sales Representativetable 604 and Customer table 602 to add the new matter from eachrespective City table 604 key to the beginning portion of eachcorresponding Customer table 602 key and Sales Representative table 604key. Specifically, for example, the model builder generates a key “KeyA”for Chicago in the City table key 606 key and modifies each key forJones, Smith and Lancaster in the Sales Representative table 604 toinclude the new “A” matter in their keys, since those SalesRepresentatives are associated with Chicago. Likewise, the model builderdoes the same for keys in the Customer table 602, i.e., for Customersassociated with Chicago. Likewise, the model builder generates a key“KeyB” for New York in the City table 606 key, modifies each key forAnderson and Baker in the Sales Representative table 604 to include thenew “B” matter in their keys, since those Sales Representatives areassociated with New York, and similarly modifies each key in theCustomer table 602, i.e., for Customers associated with New York.

It should be appreciated from the above that since the model builderbuilds the model from data in a report, both the model and the reportare empty until the user initially adds some data to the report. Thisfirst addition of data requires a new dimension to be created in allcases. Subsequent additions of data require the model builder todetermine whether to add a level to an existing dimension or create anew dimension. Creating a new dimension for new data in the report isrequired when there is only an N to N mapping of the new data to allexisting data in the report and model. That is, there is no existingdimension/hierarchy to which add the new data if there is no 1 to N or Nto 1 mapping of the new data to any existing data in the model. In theabove examples, the model builder generates a model with SalesRepresentatives, Cities and Customers in a single dimension with threelevels, such that the outer most level, i.e., bottom level, which isCustomers in these examples, has a 1 to N relationship with the secondlevel, and the second level has a 1 to N relationship with the thirdlevel. Note that this single dimension model works because of the 1 to Nrelations. If one or more of the Sales Representatives have more thanone City and one or more of the Cities have more than one SalesRepresentative, for example, then they cannot both be in the samehierarchy. Consequently, the model builder represents them in differentdimensions in the OLAP model.

Referring now to FIG. 4B, an example is shown of source data 401 inwhich one or more of the Sales Representatives have more than one Cityand one or more of the Cities have more than one Sales Representative,which is an N to N mapping. In one such N to N instance in the example,Jones is a Sales Representative assigned to La Follette and WhitneyPoint, but La Follette also has Lancaster as a Sales Representative.

FIGS. 7A through 7C illustrate the user selecting source data 401 ofFIG. 4B, wherein the user first selects Customers 408 to include in areport, then Cities 406, then Sales Representatives 404. FIGS. 7Athrough 7C illustrate that the model builder, in this circumstance,first (FIG. 7A) creates a dimension for the first data the user adds tothe report, i.e., the Customer data 408, which includes generating aCustomer table 702 and Then, when the user adds City 406 data to thereport, the model builder adds the City 406 data to a level of thedimension created for the Customer 408 data, which includes the modelbuilder now adding a City table 704 and generating keys (not shown) fortables 702 and 704 relating each City to each Customer, as shown (FIG.7B), according to an embodiment of the present invention.

Next (FIG. 7C), when the user adds the Sales Representative 404 data tothe report, the model builder creates a second dimension in the modelfor the Sales Representative 404 data, since the Sales Representativesdo not have a 1 to N relation to any of the model data. Creating thesecond dimension in the model for the Sales Representative data includesadding Sales Representative table 706 and modifying keys (not shown).

When the model builder creates a new dimension, it looks at source data401 to find cases where it can expand the new dimension by addinglevels. If there is other source data not already in the model in whichdata of one attribute has a 1 to N mapping to new data the user justadded for which the model builder created a new dimension and if noother data has an attribute with 1 to N mapping to the new data, thenthis represents a case where there is no ambiguity about what can beplaced in a next level of the new dimension. The model builder,therefore, automatically adds the source data having this one attributeto the model, in order to allow for easy navigation in the resultingreport. For example, if the user adds data having a Years attribute tothe report and the model builder generates a new dimension for the Yearsattribute, and Months is the only source data that has not been added tothe model and that has a 1 to N mapping to Years, then the model builderautomatically adds Months data as the next level in the new dimension,so that the new dimension then has Years and Months levels.

Still further, when the model builder first automatically adds sourcedata responsive to the above described new dimension and 1 to N mappingcondition, the model builder also looks at the source data 401 to findcases where it can add still more levels due to the first automaticaddition of source data. That is, the model builder looks for data thatis the only source data having 1 to N mapping to data that wasautomatically added for a new dimension. For example, if the modelbuilder finds that Days data is the only source data having 1 to Nmapping to the above described newly and automatically added Years data,then the model builder also adds the Days data as a new level to the newdimension, so that the new dimension then has Years, Month and Dayslevels.

The above examples have shown dimensions and dimension tables, whichprovide categories for a report. However, metrics tend to be a centralfocus of reports. A user may select dimensions prior to selecting ametric, but a user typically selects at least one metric to include in areport. FIGS. 8A through 8D provide an example of this metric aspect,where the example is based on source data of FIG. 4C. FIG. 8B alsoillustrates that adding a new portion of a dimension table for creatinga new hierarchy, as described for a snowflake schema in the earlierexamples shown in FIGS. 5B and 5C, FIGS. 6B and 6C and FIG. 7B, may bedone for a star schema by generating an extra column in an existingdimension table, according to embodiments of the present invention.

As shown in FIG. 8A together with FIG. 4C, responsive to the userselecting first data for the report, which is Sales Representatives 404in this example, the model builder creates a first dimension, whichincludes generating a dimension table 802 for Sales Representatives 404,including keys. In the illustrated instance the user then selectsadditional data for the report, where the additional data is not ametric, as shown in FIG. 8B. In this example, the user selects City data406. Since there is a 1 to N relationship between Sales Representatives404 and Cities 406, the model builder adds the City data 406 to theexisting dimension table 802 rather than creating a new table. Note thathere and in the other examples presented, when the user selects portionsof data 401 for the report, the model builder also updates OLAP modelmetadata stored in a data structure such as catalog 503 outside schema501 as shown in FIGS. 5A, 5B and 5C, such as the names of the levels inthe hierarchy (e.g., Sales Representative, City).

Next, as illustrated in FIG. 8C, responsive to the user inserting ametric column from the source data, e.g., Sales 412 quantity, the modelbuilder creates a fact table 804 having a column for the Sales 412quantity and having keys in a key column, where each key uniquely linksits row of Sales 412 quantity to a corresponding row of SalesRepresentatives 404 and City 406 dimensions in dimension table 802 (alsoreferred to as “D1”), i.e., in the star schema again.

Note that source data 401 in FIG. 4C has rows that include duplicatedata items, such as rows containing Sales Rep=Jones and City=LaFollette, for example, and that in the illustrated example of FIGS. 8Athrough 8D, when the model builder generates the fact table 804, itaggregates data in the fact table 804 rows responsive to the numericdata 412 that the user selected to include in the report. That is, sincethe user selected Sales quantities 412 but not individual products 410,and since the user selected Cities 406 and Sales Representatives 404 butnot Customers 408, the model builder adds Sales quantities 412 from morethan one source data 401 row to include in a single row of fact table804. Specifically, for each row in fact table 804 the model builder addsSales quantities in source data 401 for both types of Products 412,i.e., cars and vans, and for all Customers in the row's correspondingCity. So in fact table 804 first row in FIG. 8C, for example, the Salesquantity is 10, which is the sum of the 4 car sales and 2 van sales forSaetek, and 2 car sales and 2 van sales for Felectrics in four of thesource data 401 rows.

Alternatively, the model builder may generate rows in fact table 804 foreach of these four rows, resulting in a fact table containing the samenumber of rows as the numeric data 412 in the originally selected sourcedata 401. Even without aggregating numeric data, however, the portion ofsource data 401 that the model builder includes in the model is stilllimited to only the portion the user selected, so that in the example ofFIG. 8C the model still does not include columns in dimension tables forCustomers 408 and Product types 410.

Next, as shown in FIG. 8D, responsive to the user adding anothernon-metric column from source data 401, i.e., Product 410 type in thisexample, the model builder creates a new dimension. In this case, sinceProduct 410 has an N to N relationship with the other data 402, 404,406, 408 and 412, the model builder creates a new dimension table 808for this dimension (also referred to as “D2”). To link this newdimension to the existing data in the model, the model builder generatesa new key for each unique Product 410, which the model builder includesin a column of dimension table 808; creates a new key column for facttable 804; and includes in the new key column of fact table 804 arespective one of the new keys for each corresponding row of Sales 412quantity. Also, since the model builder earlier aggregated Salesquantity data for car and van Product types, as explained above, andsince the user has subsequently selected to include Product types 410 inthe report, the model builder now adds rows to the fact table 804 thatit generated in FIG. 8C. That is the model builder now generates a facttable 804 shown in FIG. 8D having twice as many rows, wherein respectiveSales quantities 412 are provided for each of the two Product types 410for each Sales Representative 404 and City 406.

According to embodiments of the present invention, the model buildergenerates an OLAP model having references that point to numeric sourcedata 401 the user selects for the report, such as source data 401 celladdresses, rather than including the numeric data itself. Rather thanincluding the quantity “4” in the first row of quantities in fact table804 of FIG. 8D, for example, the model builder may include the sourcedata 401 address for that quantity, such as cell “F2” in the example.Otherwise, the model builder includes information about dimensions inthe model in some fashion, such as in the fashion presented herein forillustrating one or more embodiments of the present invention.

In another aspect, it is possible there is data that is already inanother hierarchy in the model when the user selects the data for thereport, but the data is not already in the report before the userselects it. (This may arise, for example, when the user has modified areport to delete the data, or else, for example, the user has generateda new report that doesn't use data that had previously been selected forincluding in a previous report). If this occurs, then the model buildermodule automatically removes the selected data from the other hierarchyand creates a hierarchy with the data that the selected data is nestedunder in the report.

Referring now to FIG. 9, a process 900 is illustrated for dynamicallygenerating a model for a report, which includes a computer systempresenting 904 a user with source data in a source application module,wherein the source data is available in the source application modulefor producing a report or query. User interaction with source data isdetected, which includes, for example, selections from the user received908 by the computer system indicating portions of the source data topotentially include in the report. Other interactions may include theuser performing operations on at least some of the source data in anexecuting application module, including any one or more of operationssuch as data selecting, manipulating, querying, navigating, changing,displaying, etc. A builder module of the computer system generates 912an analytic processing model for user selected portions of the sourcedata in response to the detected user interactions, e.g., the receiveduser selections.

In one instance generating 912 the analytic processing model for theuser selected portions of the source data includes the computer systembuilder module creating 920 a first dimension in the model responsive tothe computer system builder module detecting that the user has selecteda first user selected source data portion (i.e., an initial userselected source data portion) for potentially including in the reportand that the model does not yet include any existing data for thereport. In one instance, generating 916 the analytic processing modelfor the user selected portions of the source data includes the computersystem builder module selecting 924 between creating a new dimension inthe model or else creating a new level in an existing dimension in themodel responsive to the computer system builder module detecting whetherdata in the first user selected source data portion has a predeterminedrelationship with a second portion of the source data that is selectedby the user, wherein the computer system builder module selects tocreate 928 a new dimension in the model for a user selected source dataportion having an N to N mapping with existing data in the model andwherein the computer system builder module selects to create 932 a newlevel of an existing dimension when a user selected source data portionhas a 1 to N or N to 1 relationship to existing data in the model.Further, when the computer system builder module creates a new dimensionfor a user selected source data portion, the computer system buildermodule selects 934 whether to add 936 a new level for the new dimensionresponsive to the computer system builder module, wherein the level isadded responsive to detecting a 1 to N or an N to 1 mapping of thesource data portion for which the new dimension is created to othersource data that has not yet been user selected for potentiallyincluding in the report.

Process 900 further includes, according to illustrated embodiments ofthe present invention, generating 916 the report by an analyticapplication module responsive to a user query of the user selectedsource data portions, wherein the analytic application module uses theanalytic processing model to extract data for including in the reportfrom the user selected source data portions, wherein the extracted datasatisfies the query.

Once the OLAP model exists for the data in the report (and the extradata that is determined to likely be in the same dimension as data inthe report), simple operations like drill up and down can be executedwith OLAP speed. Calculations are also possible without modelalterations, including, for example, calculations such as number of carssold plus number of vans sold, as in FIG. 4C and FIGS. 8C and 8D. Muchlike a raw member, such as number of cars sold, the value shown for acalculation is in the context of the current measure in the report.Calculations are defined as artifacts in the model or directly in thereport. An OLAP application module is able to resolve these in thecontext of an OLAP model by finding values for members within acalculation and then resolving the calculation. The speed and simplicityof OLAP is provided and only requires the model to update when new datais brought into the report.

The OLAP model is saved with the report, so that the model is availableto consumers. It is advantageous that the saved model is generatedresponsive to the user creating the report and that the model is,thereby, minimized to correspond to only the data that is needed for thereport. If the report is consumed by a report consumer (a user with noauthoring capabilities), then the model is sufficient for everything theuser can do with that report, since such a user has no access to the rawdata or a fuller model. This can both save space on disk and speed upanalysis for the user.

The present invention may be a system, a method, and/or a computerprogram product. The computer program product may include a computerreadable storage medium (or media) having computer readable programinstructions thereon for causing a processor to carry out aspects of thepresent invention.

The computer readable storage medium can be a tangible device that canretain and store instructions for use by an instruction executiondevice. The computer readable storage medium may be, for example, but isnot limited to, an electronic storage device, a magnetic storage device,an optical storage device, an electromagnetic storage device, asemiconductor storage device, or any suitable combination of theforegoing. A non-exhaustive list of more specific examples of thecomputer readable storage medium includes the following: a portablecomputer diskette, a hard disk, a random access memory (RAM), aread-only memory (ROM), an erasable programmable read-only memory (EPROMor Flash memory), a static random access memory (SRAM), a portablecompact disc read-only memory (CD-ROM), a digital versatile disk (DVD),a memory stick, a floppy disk, a mechanically encoded device such aspunch-cards or raised structures in a groove having instructionsrecorded thereon, and any suitable combination of the foregoing. Acomputer readable storage medium, as used herein, is not to be construedas being transitory signals per se, such as radio waves or other freelypropagating electromagnetic waves, electromagnetic waves propagatingthrough a waveguide or other transmission media (e.g., light pulsespassing through a fiber-optic cable), or electrical signals transmittedthrough a wire.

Computer readable program instructions described herein can bedownloaded to respective computing/processing devices from a computerreadable storage medium or to an external computer or external storagedevice via a network, for example, the Internet, a local area network, awide area network and/or a wireless network. The network may comprisecopper transmission cables, optical transmission fibers, wirelesstransmission, routers, firewalls, switches, gateway computers and/oredge servers. A network adapter card or network interface in eachcomputing/processing device receives computer readable programinstructions from the network and forwards the computer readable programinstructions for storage in a computer readable storage medium withinthe respective computing/processing device.

Computer readable program instructions for carrying out operations ofthe present invention may be assembler instructions,instruction-set-architecture (ISA) instructions, machine instructions,machine dependent instructions, microcode, firmware instructions,state-setting data, or either source code or object code written in anycombination of one or more programming languages, including an objectoriented programming language such as Smalltalk, C++ or the like, andconventional procedural programming languages, such as the “C”programming language or similar programming languages. The computerreadable program instructions may execute entirely on the user'scomputer, partly on the user's computer, as a stand-alone softwarepackage, partly on the user's computer and partly on a remote computeror entirely on the remote computer or server. In the latter scenario,the remote computer may be connected to the user's computer through anytype of network, including a local area network (LAN) or a wide areanetwork (WAN), or the connection may be made to an external computer(for example, through the Internet using an Internet Service Provider).In some embodiments, electronic circuitry including, for example,programmable logic circuitry, field-programmable gate arrays (FPGA), orprogrammable logic arrays (PLA) may execute the computer readableprogram instructions by utilizing state information of the computerreadable program instructions to personalize the electronic circuitry,in order to perform aspects of the present invention.

Aspects of the present invention are described herein with reference toflowchart illustrations and/or block diagrams of methods, apparatus(systems), and computer program products according to embodiments of theinvention. It will be understood that each block of the flowchartillustrations and/or block diagrams, and combinations of blocks in theflowchart illustrations and/or block diagrams, can be implemented bycomputer readable program instructions.

These computer readable program instructions may be provided to aprocessor of a general purpose computer, special purpose computer, orother programmable data processing apparatus to produce a machine, suchthat the instructions, which execute via the processor of the computeror other programmable data processing apparatus, create means forimplementing the functions/acts specified in the flowchart and/or blockdiagram block or blocks. These computer readable program instructionsmay also be stored in a computer readable storage medium that can directa computer, a programmable data processing apparatus, and/or otherdevices to function in a particular manner, such that the computerreadable storage medium having instructions stored therein comprises anarticle of manufacture including instructions which implement aspects ofthe function/act specified in the flowchart and/or block diagram blockor blocks.

The computer readable program instructions may also be loaded onto acomputer, other programmable data processing apparatus, or other deviceto cause a series of operational steps to be performed on the computer,other programmable apparatus or other device to produce a computerimplemented process, such that the instructions which execute on thecomputer, other programmable apparatus, or other device implement thefunctions/acts specified in the flowchart and/or block diagram block orblocks.

The flowchart and block diagrams in the Figures illustrate thearchitecture, functionality, and operation of possible implementationsof systems, methods, and computer program products according to variousembodiments of the present invention. In this regard, each block in theflowchart or block diagrams may represent a module, segment, or portionof instructions, which comprises one or more executable instructions forimplementing the specified logical function(s). In some alternativeimplementations, the functions noted in the block may occur out of theorder noted in the figures. For example, two blocks shown in successionmay, in fact, be executed substantially concurrently, or the blocks maysometimes be executed in the reverse order, depending upon thefunctionality involved. It will also be noted that each block of theblock diagrams and/or flowchart illustration, and combinations of blocksin the block diagrams and/or flowchart illustration, can be implementedby special purpose hardware-based systems that perform the specifiedfunctions or acts or carry out combinations of special purpose hardwareand computer instructions.

One or more databases may be included in a host for storing andproviding access to data for the various implementations. One skilled inthe art will also appreciate that, for security reasons, any databases,systems, or components of the present invention may include anycombination of databases or components at a single location or atmultiple locations, wherein each database or system includes any ofvarious suitable security features, such as firewalls, access codes,encryption, de-encryption and the like.

The database may be any type of database, such as relational,hierarchical, object-oriented, and/or the like. A database product thatmay be used to implement the databases is IBM® DB2®, or other availabledatabase products. (IBM and DB2 are trademarks of International BusinessMachines Corporation, registered in many jurisdictions worldwide.) Thedatabase may be organized in any suitable manner, including as datatables or lookup tables.

Association of certain data may be accomplished through any dataassociation technique known and practiced in the art. For example, theassociation may be accomplished either manually or automatically.Automatic association techniques may include, for example, a databasesearch, a database merge, GREP, AGREP, SQL, and/or the like. Theassociation step may be accomplished by a database merge function, forexample, using a key field in each of the manufacturer and retailer datatables. A key field partitions the database according to the high-levelclass of objects defined by the key field. For example, a certain classmay be designated as a key field in both the first data table and thesecond data table, and the two data tables may then be merged on thebasis of the class data in the key field. In this embodiment, the datacorresponding to the key field in each of the merged data tables ispreferably the same. However, data tables having similar, though notidentical, data in the key fields may also be merged by using AGREP, forexample.

The host may provide a suitable website or other internet-basedgraphical user interface accessible by users. In one embodiment,Netscape web server, IBM® Websphere® Internet tools suite, an IBM DB2,universal database platform and a Sybase database platform are used inconjunction with a Sun Solaris operating system platform. (IBM andWebSphere are trademarks of International Business Machines Corporation,registered in many jurisdictions worldwide.) Additionally, componentssuch as JBDC drivers, IBM connection pooling and IBM MQ seriesconnection methods may be used to provide data access to severalsources. The term webpage as it is used herein is not meant to limit thetype of documents and application modules that might be used to interactwith the user. For example, a typical website might include, in additionto standard HTML documents, various forms, Java applets, Javascript,active server pages (ASP), Java Server Pages (JSP), common gatewayinterface scripts (CGI), extensible markup language (XML), dynamic HTML,cascading style sheets (CSS), helper application modules, plug-ins, andthe like.

While this specification contains many specifics, these should not beconstrued as limitations on the scope of the invention or of what can beclaimed, but rather as descriptions of features specific to particularimplementations of the invention. Certain features that are described inthis specification in the context of separate implementations can alsobe implemented in combination in a single implementation. Conversely,various features that are described in the context of a singleimplementation can also be implemented in multiple implementationsseparately or in any suitable sub combination. Moreover, althoughfeatures can be described above as acting in certain combinations andeven initially claimed as such, one or more features from a claimedcombination can in some cases be excised from the combination, and theclaimed combination can be directed to a subcombination or variation ofa subcombination.

Similarly, while operations are depicted in the drawings in a particularorder, this should not be understood as requiring that such operationsbe performed in the particular order shown or in sequential order, orthat all illustrated operations be performed, to achieve desirableresults. In certain circumstances, multitasking and parallel processingcan be advantageous. Moreover, the separation of various systemcomponents in the implementations described above should not beunderstood as requiring such separation in all implementations, and itshould be understood that the described program components and systemscan generally be integrated together in a single software product orpackaged into multiple software products.

Those skilled in the art having read this disclosure will recognize thatchanges and modifications may be made to the embodiments withoutdeparting from the scope of the present invention.

It should be appreciated that the particular implementations shown anddescribed herein are illustrative of the invention and its best mode andare not intended to otherwise limit the scope of the present inventionin any way. Other variations are within the scope of the followingclaims.

The actions recited in the claims can be performed in a different orderand still achieve desirable results. Likewise, the processes depicted inthe accompanying figures do not necessarily require the particular ordershown, or sequential order, to achieve desirable results. In certainimplementations, multitasking and parallel processing can beadvantageous.

Benefits, other advantages, and solutions to problems have beendescribed above with regard to specific embodiments. However, thebenefits, advantages, solutions to problems, and any element(s) that maycause any benefit, advantage, or solution to occur or become morepronounced are not to be construed as critical, required, or essentialfeatures or elements of any or all the claims.

As used herein, the terms comprises, comprising, or any other variationthereof, are intended to cover a non-exclusive inclusion, such that aprocess, method, article, or apparatus that comprises a list of elementsdoes not include only those elements but may include other elements notexpressly listed or inherent to such process, method, article, orapparatus. Further, no element described herein is required for thepractice of the invention unless expressly described as essential orcritical.

The terminology used herein is for the purpose of describing particularembodiments only and is not intended to be limiting of the invention. Asused herein, the singular forms “a”, “an” and “the” are intended toinclude the plural forms as well, unless the context clearly indicatesotherwise. It will be further understood that the terms “comprises”and/or “comprising,” when used in this specification, specify thepresence of stated features, integers, steps, operations, elements,and/or components, but do not preclude the presence or addition of oneor more other features, integers, steps, operations, elements,components, and/or groups thereof.

The corresponding structures, materials, acts, and equivalents of allmeans or step plus function elements in the claims below are intended toinclude any structure, material, or act for performing the function incombination with other claimed elements as specifically claimed.

Many modifications and variations will be apparent to those of ordinaryskill in the art without departing from the scope and spirit of theinvention. For example, examples have been presented herein in which auser selects data using a spreadsheet application module, but theinvention is not limited to user interaction with data via aspreadsheet. In general, a user may interact with data that isstructured such that the user can select portions of the data that gotogether, such as in a table or tables. Some file formats like CSVpresent tabular data that is suitably structured, for example. Exampleshave been presented herein in which a report is generated by a tool thatembodies the invention. The tool may present both the report and a viewof the underlying data that permits user interaction with the sourcedata, including data selection. IBM Watson Analytics is a good exampleof such a tool. The tool may also be implemented as a spreadsheetapplication module, such as by a spreadsheet plugin for example. Also adatabase application module may present a view of data in a manner thatpermits a user to interact with the source data as described herein.

The embodiments presented herein were chosen and described in order tobest explain the principles of the invention and the practicalapplication and to enable others of ordinary skill in the art tounderstand the invention for various embodiments with variousmodifications as are suited to the particular use contemplated. Thedescription of the present invention has been presented for purposes ofillustration and description, but is not intended to be exhaustive orlimited to the invention in the form disclosed.

What is claimed is:
 1. A method for dynamically generating a model for areport, the method comprising: presenting a user with source data by asource application module, the source data being available for producinga report and being presented to the user by the source applicationmodule in a unitary structure including rows and columns of data;receiving selections from the user of first, second and third ones ofthe columns, where a nesting structure of the second column datarelative to the first column data indicates the second column data is ofa dimension that is a level of the first column data, and where a lackof nesting structure of the third column data relative to the firstcolumn data indicates the third column data is of a dimension that isseparate from a dimension of the first column data; and generating, by acomputer system builder module in response to receiving the userselections, a snowflake-schema-based, analytic processing model, whereinfor the selected first, second and third columns the builder moduleautomatically creates respective first, second and third tables andtable mapping structures, where the tables are created regardless of anynesting structure of the first, second and third column data, and wherethe mapping structure for the second table designates, in response tonesting structure of the second column data relative to the first columndata, that the second table is for a dimension that is a level of adimension of the first table, and where the mapping structure for thethird table designates, in response to the lack of nesting structure ofthe third column data relative to the first column data, that the thirdtable is for a dimension that is separate from the first table'sdimension.
 2. The method of claim 1, wherein generating the analyticprocessing model for the user selected columns includes the computersystem builder module creating the first dimension in the modelresponsive to the computer system builder module detecting that the userhas selected the first column for potentially including in the reportand that the model does not yet include any existing data for thereport.
 3. The method of claim 1, wherein the computer system buildermodule selects to create, in the mapping structure for the second table,the dimension for the second table that is a level of a dimension of thefirst table responsive to the nesting structure of the second columndata being a 1 to N or N to 1 mapping relative to the first column data.4. The method of claim 1, wherein the computer system builder moduleselects to create, in the mapping structure for the third table, theseparate dimension for the third table responsive to the nestingstructure of the third column data being an N to N mapping relative tothe first column data.
 5. The method of claim 1, wherein in addition tothe computer system builder module creating the separate dimension inthe mapping structure for the third table, the computer system buildermodule adds, in the mapping structure for the third table, a level forthe third table's dimension responsive to the computer system buildermodule detecting a 1 to N or an N to 1 mapping of the third column datato source data that has not yet been user selected for potentiallyincluding in the report.
 6. The method of claim 1, wherein the methodfurther comprises: receiving a selection from the user of a fourth oneof the columns, wherein generating the analytic processing model for theuser selected columns includes the computer system builder moduleselecting between creating, for the fourth column, a new dimension inthe model or else creating a new level in an existing dimension in themodel responsive to the computer system builder module detecting whetherdata in the first three columns have only N to N mappings relative todata in the fourth column.
 7. The method of claim 1, comprising:generating the report by an analytic application module responsive to auser query of user selected columns, wherein the analytic applicationmodule uses the analytic processing model to extract data for includingin the report from the user selected columns, wherein the extracted datasatisfies the query.
 8. A computer program product for dynamicallygenerating a model for a report, the computer program product includinga computer readable storage medium having instructions stored thereonfor execution by a computer system, wherein the instructions, whenexecuted by the computer system, cause the computer system to implementa method comprising: presenting a user with source data by a sourceapplication module, the source data being available for producing areport and being presented to the user by the source application modulein a unitary structure including rows and columns of data; receivingselections from the user of first, second and third ones of the columns,where a nesting structure of the second column data relative to thefirst column data indicates the second column data is of a dimensionthat is a level of the first column data, and where a lack of nestingstructure of the third column data relative to the first column dataindicates the third column data is of a dimension that is separate froma dimension of the first column data; and generating, by a computersystem builder module in response to receiving the user selections, asnowflake-schema-based, analytic processing model, wherein for theselected first, second and third columns the builder moduleautomatically creates respective first, second and third tables andtable mapping structures, where the tables are created regardless of anynesting structure of the first, second and third column data, and wherethe mapping structure for the second table designates, in response tonesting structure of the second column data relative to the first columndata, that the second table is for a dimension that is a level of adimension of the first table, and where the mapping structure for thethird table designates, in response to the lack of nesting structure ofthe third column data relative to the first column data, that the thirdtable is for a dimension that is separate from the first table'sdimension.
 9. The computer program product of claim 8, whereingenerating the analytic processing model for the user selected columnsincludes the computer system builder module creating the first dimensionin the model responsive to the computer system builder module detectingthat the user has selected the first column for potentially including inthe report and that the model does not yet include any existing data forthe report.
 10. The computer program product of claim 8, wherein thebuilder module instructions are configured to select to create, in themapping structure for the second table, the dimension for the secondtable that is a level of a dimension of the first table responsive tothe nesting structure of the second column data being a 1 to N or N to 1mapping relative to the first column data.
 11. The computer programproduct of claim 8, wherein the builder module instructions areconfigured to select to create, in the mapping structure for the thirdtable, the separate dimension for the third table responsive to thenesting structure of the third column data being an N to N mappingrelative to the first column data.
 12. The computer program product ofclaim 8, wherein in addition to the computer system builder modulecreating the separate dimension in the mapping structure for the thirdtable, the computer system builder module adds, in the mapping structurefor the third table, a level for the third table's dimension responsiveto the computer system builder module detecting a 1 to N or an N to 1mapping of the third column data to source data that has not yet beenuser selected for potentially including in the report.
 13. The computerprogram product of claim 8, wherein the instructions, when executed bythe computer system, cause the computer to implement a methodcomprising: receiving a selection from the user of a fourth one of thecolumns, wherein generating the analytic processing model for the userselected columns includes the computer system builder module selectingbetween creating, for the fourth column, a new dimension in the model orelse creating a new level in an existing dimension in the modelresponsive to the computer system builder module detecting whether datain the first three columns have only N to N mappings relative to data inthe fourth column.
 14. The computer program product of claim 8, whereinthe instructions, when executed by the computer system, cause thecomputer system to implement a method comprising: generating the reportresponsive to a user query of user selected columns and responsive toanalytic application module instructions, wherein the analyticapplication module instructions are configured to use the analyticprocessing model to extract data for including in the report from theuser selected columns, wherein the extracted data satisfies the query.15. A system for dynamically generating a model for a report, the systemcomprising: a processor; and a computer readable storage mediumconnected to the processor, wherein the computer readable storage mediumhas stored thereon a program for controlling the processor, and whereinthe processor is operative with the program to execute the program for:presenting a user with source data by a source application module, thesource data being available for producing a report and being presentedto the user by the source application module in a unitary structureincluding rows and columns of data; receiving selections from the userof first, second and third ones of the columns, where a nestingstructure of the second column data relative to the first column dataindicates the second column data is of a dimension that is a level ofthe first column data, and where a lack of nesting structure of thethird column data relative to the first column data indicates the thirdcolumn data is of a dimension that is separate from a dimension of thefirst column data; and generating, by a computer system builder modulein response to receiving the user selections, a snowflake-schema-based,analytic processing model, wherein for the selected first, second andthird columns the builder module automatically creates respective first,second and third tables and table mapping structures, where the tablesare created regardless of any nesting structure of the first, second andthird column data, and where the mapping structure for the second tabledesignates, in response to nesting structure of the second column datarelative to the first column data, that the second table is for adimension that is a level of a dimension of the first table, and wherethe mapping structure for the third table designates, in response to thelack of nesting structure of the third column data relative to the firstcolumn data, that the third table is for a dimension that is separatefrom the first table's dimension.
 16. The system of claim 15, whereingenerating the analytic processing model for the user selected columnsincludes the computer system builder module creating the first dimensionin the model responsive to the computer system builder module detectingthat the user has selected the first column for potentially including inthe report and that the model does not yet include any existing data forthe report.
 17. The system of claim 15, wherein the builder moduleinstructions are configured to select to create, in the mappingstructure for the second table, the dimension for the second table thatis a level of a dimension of the first table responsive to the nestingstructure of the second column data being a 1 to N or N to 1 mappingrelative to the first column data.
 18. The system of claim 15, whereinthe computer system builder module selects to create, in the mappingstructure for the third table, the separate dimension for the thirdtable responsive to the nesting structure of the third column data beingan N to N mapping relative to the first column data
 19. The system ofclaim 15, wherein the processor is operative with the program to executethe program for: adding, in the mapping structure for the third table, alevel for the third table's dimension responsive to the computer systembuilder module detecting a 1 to N or an N to 1 mapping of the thirdcolumn data to source data that has not yet been user selected forpotentially including in the report.
 20. The system of claim 15, whereinthe processor is operative with the program to execute the program for:generating the report by an analytic application module responsive touser query of user selected columns, wherein the analytic applicationmodule uses the analytic processing model to extract data for includingin the report from the user selected columns, wherein the extracted datasatisfies the query.